/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.LandStatus;
import com.ntb.entities.Lands;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author AnhTuan
 */
public class LandsDAOImpl extends DatabaseConnection implements LandsDAO {

    public Iterator getCountList(int year) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countLandsMonth(?)}");
            cst.setInt(1, year);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                list.add(new int[]{rs.getInt(1), rs.getInt(2)});
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int countAll() {
        try {
            openConnection();
            ResultSet rs = getResultset("Select Count(LandID) from Lands");
            int result = 0;
            if (rs.next()) {
                result = rs.getInt(1);
            }
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Iterator<Lands> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From Lands");
            while (rs.next()) {
                Lands row = new Lands();
                row.setLandID(rs.getInt("LandID"));
                row.setPlanID(rs.getInt("PlanID"));
                row.setLocationID(rs.getInt("LocationID"));
                row.setAddress(rs.getString("address"));
                row.setPurchasedCost(rs.getInt("PurchasedCost"));
                row.setPurchasedDate(rs.getDate("PurchasedDate"));
                row.setNearByLandMarks(rs.getString("NearByLandMarks"));
                row.setLandArea(rs.getInt("LandArea"));
                row.setStatusCode(rs.getString("StatusCode"));
                row.setStatusChangedDate(rs.getTimestamp("StatusChangedDate"));
                row.setBuildingPermitID(rs.getInt("BuildingPermitID"));
                row.setOccupancyPermitID(rs.getInt("OccupancyPermitID"));

                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<Lands> getList(String landId, String address, int locationId, int planId, int areaFrom, int areaTo, int purchasedCostFrom, int purchasedCostTo, String purchasedDateFrom, String purchasedDateTo, String status, int page) {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageLands(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, landId);
            cst.setString(2, address);
            cst.setInt(3, locationId);
            cst.setInt(4, planId);
            cst.setInt(5, areaFrom);
            cst.setInt(6, areaTo);
            cst.setInt(7, purchasedCostFrom);
            cst.setInt(8, purchasedCostTo);
            cst.setString(9, purchasedDateFrom);
            cst.setString(10, purchasedDateTo);
            cst.setString(11, status);
            cst.setInt(12, page);
            cst.setInt(13, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                Lands row = new Lands();
                row.setLandID(rs.getInt("LandID"));
                row.setPlanID(rs.getInt("PlanID"));
                row.setPlanName(rs.getString("PlanName"));
                row.setLocationID(rs.getInt("LocationID"));
                row.setLocationName(rs.getString("LocationName"));
                row.setAddress(rs.getString("address"));
                row.setPurchasedCost(rs.getInt("PurchasedCost"));
                row.setPurchasedDate(rs.getDate("PurchasedDate"));
                row.setLandArea(rs.getInt("LandArea"));
                row.setStatusCode(rs.getString("StatusCode"));

                list.add(row);
            }
            closeConnection();
            if (list.size() > 0) {
                return list.iterator();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public int getPageCount(String landId, String address, int locationId, int planId, int areaFrom, int areaTo, int purchasedCostFrom, int purchasedCostTo, String purchasedDateFrom, String purchasedDateTo, String status) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageLands(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
            cst.setString(1, landId);
            cst.setString(2, address);
            cst.setInt(3, locationId);
            cst.setInt(4, planId);
            cst.setInt(5, areaFrom);
            cst.setInt(6, areaTo);
            cst.setInt(7, purchasedCostFrom);
            cst.setInt(8, purchasedCostTo);
            cst.setString(9, purchasedDateFrom);
            cst.setString(10, purchasedDateTo);
            cst.setString(11, status);
            cst.setInt(12, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public Lands getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select Lands.*, Lands.LandArea * Locations.PresentCost AS PresentCost, LocationName, PlanName From Lands " +
                    "LEFT JOIN Locations " +
                    "ON Lands.LocationID = Locations.LocationID " +
                    "LEFT JOIN LandPlansLaid " +
                    "ON Lands.PlanID = LandPlansLaid.PlanID Where LandID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            Lands row = new Lands();
            if (rs.next()) {
                row.setLandID(rs.getInt("LandID"));
                row.setPlanID(rs.getInt("PlanID"));
                row.setPlanName(rs.getString("PlanName"));
                row.setLocationID(rs.getInt("LocationID"));
                row.setLocationName(rs.getString("LocationName"));
                row.setAddress(rs.getString("Address"));
                row.setPurchasedCost(rs.getInt("PurchasedCost"));
                row.setPurchasedDate(rs.getDate("PurchasedDate"));
                row.setPresentCost(rs.getInt("PresentCost"));
                row.setNearByLandMarks(rs.getString("NearByLandMarks"));
                row.setLandArea(rs.getInt("LandArea"));
                row.setStatusCode(rs.getString("StatusCode"));
                row.setStatusChangedDate(rs.getTimestamp("StatusChangedDate"));
                row.setBuildingPermitID(rs.getInt("BuildingPermitID"));
                row.setOccupancyPermitID(rs.getInt("OccupancyPermitID"));

            }
            closeConnection();
            return row;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean checkExist(int landId) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select LandID From Lands Where LandID=?");
            pst.setInt(1, landId);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public int add(Lands object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into Lands(Address, LocationID, PlanID, PurchasedCost, PurchasedDate," +
                    " NearByLandMarks, LandArea, StatusCode, StatusChangedDate)" +
                    " Values(?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, object.getAddress());
            pst.setInt(2, object.getLocationID());
            pst.setInt(3, object.getPlanID());
            pst.setInt(4, object.getPurchasedCost());
            pst.setDate(5, object.getPurchasedDate());

            pst.setString(6, object.getNearByLandMarks());
            pst.setInt(7, object.getLandArea());
            pst.setString(8, "CR");
            Timestamp date = new Timestamp(new java.util.Date().getTime());
            pst.setTimestamp(9, date);

            int result = pst.executeUpdate();

            ResultSet rs = pst.getGeneratedKeys();
            rs.next();

            LandStatusDAO statusDao = new LandStatusDAOImpl();
            LandStatus status = new LandStatus();
            status.setLandID(rs.getInt(1));
            status.setChangedDate(date);
            status.setStatusCode("CR");
            statusDao.add(status);

            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(Lands object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Lands Set Address = ?, LocationID = ?, PlanID = ?, PurchasedCost = ?, PurchasedDate = ?, " +
                    "NearByLandMarks=?, LandArea=? Where LandID=?");
            pst.setString(1, object.getAddress());
            pst.setInt(2, object.getLocationID());
            pst.setInt(3, object.getPlanID());
            pst.setInt(4, object.getPurchasedCost());
            pst.setDate(5, object.getPurchasedDate());

            pst.setString(6, object.getNearByLandMarks());
            pst.setInt(7, object.getLandArea());
            pst.setInt(8, object.getLandID());

            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateStatus(int landID, String statusCode) {
        try {
            LandStatusDAO statusDao = new LandStatusDAOImpl();
            LandStatus status = new LandStatus();
            status.setLandID(landID);
            status.setChangedDate(new Timestamp(new java.util.Date().getTime()));
            status.setStatusCode(statusCode);
            if (statusDao.add(status) > 0) {
                openConnection();
                PreparedStatement pst = conn.prepareStatement("Update Lands Set StatusCode = ?, StatusChangedDate = ? " +
                        "Where LandID=?");
                pst.setString(1, statusCode);
                pst.setTimestamp(2, status.getChangedDate());
                pst.setInt(3, landID);

                int result = pst.executeUpdate();
                closeConnection();
                return result;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateOccupancyPermitID(int landID, int occupancyPermitID) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Lands set OccupancyPermitID = ? " +
                    "Where LandID=?");
            pst.setInt(1, occupancyPermitID);
            pst.setInt(2, landID);

            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int updateBuildingPermitID(int landID, int buildingPermitID) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update Lands set BuildingPermitID = ? " +
                    "Where LandID=?");
            pst.setInt(1, buildingPermitID);
            pst.setInt(2, landID);

            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete Lands Where LandID = ? " +
                    "AND ? NOT IN (SELECT LandID FROM Buildings)");
            pst.setInt(1, id);
            pst.setInt(2, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int deleteIn(String arrayIds) {
        try {
            openConnection();
            String sql = "Delete Categories Where CategoryID in (" + arrayIds + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
